# encoding = utf-8
import xlrd
import pandas as pd
import requests
import json

class DealExcel:
    def __init__(self, file_name):
        self.order = [
            'c_enterprise_name', 'c_legal_person', 'f_register_capital', 'capital_type', 'd_create_date', 'c_province', 'c_city',
            'c_area', 'c_phone', 'c_address', 'c_site', 'c_remark', 'c_manage_range', 'c_sale_type'
        ]
        self.columns_map = {
            'c_enterprise_name': '企业名称',
            "c_legal_person": "法人",
            'f_register_capital': '注册资本',
            'capital_type': "注册资本单位",
            "d_create_date": "成立时间",
            'c_province': '省',
            'c_city': '市',
            'c_area': '区',
            'c_site': "网站",
            'c_phone': '联系电话',
            'c_address': '详细地址',
            'c_remark': '备注',
            'c_manage_range': "经营范围",
            'c_sale_type': '可销售类型',
        }
        self.tables = []
        self.new_tables = []
        self.fileName = file_name
        self.not_match_area_arr = []
        #去重用的列表
        self.enterpriseNameArr = []
        self.filterNames = ["兽", "动物", "水产", "鱼", "渔", "禽", "畜", "动保", "农牧", "农药"]
        self.replace_list = ["一般", "三来一补", "一次", "二手", "三维", "一体化", "第一类增值", "第二类增值", "第一类非药品类",
                             "第一类中的非药品类", "三轮", "二类精神", "三类精神", "一类精神", "第一类药品", "第二类药品", "第三类药品",
                             "三农", "三棱", "三项", "二甲", "三甲", "一类易制毒化学品", "二氧", "限二类", "三氧", "三氮", "三乙", "二酸二乙酯",
                             "三小", "一体"]
        self.sale_list = ["售", "经营", "批发", "销", "卖", "出口", "进出口"]

        self.medecine_list = ["药品", "医药", "中药", "药物", "西药", "中成药", "草药", "原料药", "药材", "片剂", "丸剂", "颗粒剂", "注射剂", "粉针剂", "新药", "口服液",
                              "北药", "藏药"]

        self.not_medicine_list = ["医药技术", "医药科技", "医药咨询", "医药包装", "药品包装", "不含诊疗活动及药品销售", "非药物", "非医药", "非药品", "不含药品", "不含医药",
                                  "医药检测", "药物检测", "药品检测", "新药包装", "不含食品药品", "不含食品、药品", "医药信息", "医药医疗咨询", "药品外包装"]
        self.one_list = ["一", "Ι", "Ⅰ"]
        self.two_list = ["二", "II", "Ⅱ"]
        self.three_list = ["三", "III", "Ⅲ"]
        self.replace_register_list = ["万", "(", ")", "元", "美", "日", "欧", "港", "香", "英镑", "法国法郎", "瑞士法郎",
                                      "澳大利亚", "新加坡", "新台湾", "新", "加", "德国马克", "挪威克朗", "韩", "韩国","韩国圆", "韩国元"]
        self.other_list = ["医用口罩", "防护服", "义齿", "止血带", "棉签", "轮椅", "病床", "医用床架", "助听器", "坐便椅", "避孕套", "安全套",
                           "治疗仪", "医用拐杖", "护理床", "医用电子仪器", "医用超声仪器"]
        self.last_yes_list = ["器械", "医疗器材", "医疗用品"]
        self.last_no_list = ["不含医疗器械", "医疗器械除外", "不含药品、医疗器械", "医疗器械维修", "医疗器械外包装"]
        self.not_is_three_arr = ["三类医疗器械除外", "不含三类医疗器械", "第三类除外"]
        self.not_is_two_arr = ["二类医疗器械除外", "二、三类医疗器械除外", "不含二类医疗器械", "第二类除外"]
        self.not_is_one_arr = ["一类医疗器械除外",  "不含一类医疗器械", "第一类除外"]

    # 读取表格中的数据，并处理
    def readExcel(self):
        print("正在读取并处理数据...")
        wb1 = xlrd.open_workbook(filename=self.fileName)
        sheet1 = wb1.sheet_by_index(0)
        i = 3
        while i < sheet1.nrows:
            data = {}
            enterpriseName = str(sheet1.row(i)[0].value)
            if self.check_enterprise_name(enterpriseName):
                data["c_enterprise_name"] = enterpriseName
                data["c_legal_person"] = sheet1.row(i)[2].value
                data["f_register_capital"] = sheet1.row(i)[3].value
                data["d_create_date"] = sheet1.row(i)[5].value
                data["c_province"] = sheet1.row(i)[8].value
                data["c_city"] = sheet1.row(i)[9].value
                data["c_area"] = sheet1.row(i)[10].value
                data["c_phone"] = sheet1.row(i)[11].value
                data["c_address"] = sheet1.row(i)[23].value
                data["c_site"] = sheet1.row(i)[25].value
                data["c_manage_range"] = sheet1.row(i)[26].value
                data["c_remark"] = ""
                self.enterpriseNameArr.append(enterpriseName)
                self.deal_legal_person(data)
                self.deal_site(data)
                self.checkArea(data)
                self.deal_register_capital(data)
                self.match_sale_type(data)
                # self.check_site(data)
                if data["c_sale_type"] != "5":
                    self.tables.append(data)
            i += 1

    # 匹配销售类型
    def match_sale_type(self,data):
        manageRange = str(data["c_manage_range"])
        manageRange = self.replace_manage_range(manageRange)
        # Ⅲ、III
        # Ⅱ
        # II、Ⅰ、I
        manageRange = self.replace_list1(manageRange)
        if "；" in manageRange:
            rangeList = manageRange.split("；")
        else:
            rangeList = manageRange.split("，")
        s = ""
        for range in rangeList:
            if "不含药品、医疗器械" in range:
                continue
            if self.check_sale(range):
                # I
                if self.one_check(range) and "1" not in s:
                    s += ",1"
                if self.two_check(range) and "2" not in s:
                    s += ",2"
                if self.three_check(range) and "3" not in s:
                    s += ",3"
                if self.medicine_check_yes(range) and self.medicine_check_no(range) and "4" not in s:
                    s += ",4"
        if s.startswith(","):
            s = s[1:]
        if s == "":
            for range in rangeList:
                if (self.last_check_yes(range) and self.last_check_no(range)) or self.check_other(
                        range):
                    s = "1"
                    break
                # elif :
                #     s = "1"
                #     break
            if s == "":
                s = "5"
        data["c_sale_type"] = s


    # 校验区域，并把错误的区域写入文件
    def checkArea(self,data):
        province = data["c_province"]
        city = data["c_city"]
        area = data["c_area"]
        with open("new_province.json", "r", encoding="UTF-8") as json_object:
            list = json.load(json_object)
            flag = False
            for i in list:
                if province == i["label"]:
                    for j in i["children"]:
                        if city == j["label"]:
                            if "children" in j.keys():
                                for k in j["children"]:
                                    if area == k["label"]:
                                        flag = True
                                        break
                                if not flag:
                                    if area not in self.not_match_area_arr:
                                        self.not_match_area_arr.append(area)
                        if flag:
                            break
                if flag:
                    break
        with open('未匹配区域{}.txt'.format(str(self.fileName).replace(".xlsx", "")), 'w') as f:
            f.write("没有匹配到的区域如下:>>>>" + '\r')
            for area in self.not_match_area_arr:
                f.write(area+"\n")
        f.close()
    # 根据企业名称过滤正常的公司
    def check_enterprise_name(self, enterprise_name):
        if enterprise_name not in self.enterpriseNameArr:
            for i in self.filterNames:
                if i in enterprise_name:
                    return False
            if enterprise_name.endswith("公司") and not enterprise_name.endswith("分公司") and not enterprise_name.endswith("支公司"):
                return True
            else:
                return False
        else:
            return False
    # 经营范围里面的其他词语
    def check_other(self, manageRange):
        for i in self.other_list:
            if i in manageRange:
                return True
        return False
    # 替换经营范围里面的罗马数字
    def replace_list1(self, manageRange):
        manageRange = manageRange.replace("Ⅲ", "三").replace("III", "三").replace("Ⅱ", "二").replace("II", "二").replace(
            "Ⅰ", "一").replace("I", "一")
        return manageRange
    # 判断是否有销售在有关词语
    def check_sale(self,range):
        for sale in self.sale_list:
            if sale in range:
                return True
        return False

    # 是第一类医疗器械
    def one_check(self, range):
        for one in self.one_list:
            if one in range and self.not_is_one(range):
                return True
        return False

    # 一类除外的字眼，不属于一类
    def not_is_one(self, range):
        for one in self.not_is_three_arr:
            if one in range:
                return False
        return True


    # 是第二类医疗器械
    def two_check(self, range):
        for one in self.two_list:
            if one in range and self.not_is_two(range):
                return True
        return False

    # 二类除外的字眼，不属于二类
    def not_is_two(self, range):
        for one in self.not_is_three_arr:
            if one in range:
                return False
        return True

    # 是第三类医疗器械
    def three_check(self, range):
        for one in self.three_list:
            if one in range and self.not_is_three(range):
                return True
        return False

    # 三类除外的字眼，不属于三类
    def not_is_three(self, range):
        for one in self.not_is_three_arr:
            if one in range:
                return False
        return True

    # 不是药品
    def medicine_check_no(self, range):
        for medicine in self.not_medicine_list:
            if medicine in range:
                return False
        return True
    # 是药品
    def medicine_check_yes(self, range):
        for medicine in self.medecine_list:
            if medicine in range:
                return True
        return False
    # 替换经营范围中的混淆词语
    def replace_manage_range(self, manageRange):
        for replace_str in self.replace_list:
            manageRange = manageRange.replace(replace_str, "")
        return manageRange
    # 最后判断是否有医疗器械的词语
    def last_check_yes(self,range):
        for item in self.last_yes_list:
            if item in range:
                return True
        return False
    # 最后判断是否包含  医疗器械除外的词语
    def last_check_no(self,range):
        for item in self.last_no_list:
            if item in range:
                return False
        return True

    # 校验网站，目前没用
    def check_site(self, data):
        site = data["c_site"]
        site_prefix = "http://"
        if site != "-":
            if "http" not in site:
                temp_site = site_prefix + site
            try:
                res = requests.get(temp_site, timeout=0.05)
                if res.status_code != 200:
                    site = "-"
            except:
                site = "-"
            print("已对{}的网站校验过".format(data["c_enterprise_name"]))
        data["c_site"] = site

    # 处理注册资本
    def deal_register_capital(self, data):
        f_register_capital = str(data["f_register_capital"])
        if data["f_register_capital"] == "-":
            data["capital_type"] = "万元"
        if "(" in f_register_capital and ")" in f_register_capital:
            start = f_register_capital.find("(")
            end = f_register_capital.find(")")
            #美元、港元
            unit = f_register_capital[start+1:end]
            data["capital_type"] = "万" + unit
            # 万(元)
        for regeister in self.replace_register_list:
            if regeister in f_register_capital:
                f_register_capital = f_register_capital.replace(regeister, "")
        data["f_register_capital"] = f_register_capital

    #法人姓名超过30，截取到30
    def deal_legal_person(self, data):
        legal_person = str(data["c_legal_person"])
        if len(legal_person) > 30:
            data["c_legal_person"] = legal_person[0:30]

    # 网站长度超过30直接不要
    def deal_site(self, data):
        site = str(data["c_site"]).replace("http://", "")
        if len(site) > 30:
            data["c_site"] = "-"
        else:
            data["c_site"] = site

    def deal_phone(self, data):
        phone = str(data["c_phone"])
        if phone is None:
            data["c_phone"] = "-"


    # 把处理过的数据写入新的表格
    def writeExcel(self):
        # 将字典列表转换为DataFrame
        pf = pd.DataFrame(list(self.tables))
        pf = pf[self.order]
        # 将列名替换为中文
        pf.rename(columns=self.columns_map, inplace=True)
        # 指定生成的Excel表格名称
        file_path = pd.ExcelWriter('处理后的' + self.fileName)
        # 替换空单元格
        pf.fillna(' ', inplace=True)
        # 输出
        pf.to_excel(file_path, encoding='utf-8', index=False)
        # 保存表格
        file_path.save()


if __name__ == '__main__':
    file_name = input("请输入文件名称")
    dealexcel = DealExcel(file_name)
    dealexcel.readExcel()
    dealexcel.writeExcel()
    print("数据处理，写入完成~")
